using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.Data.Common;

namespace BTS_Utilities
{
    /// <summary>
    /// This Class is primarily used to create a new XSD for a Biztalk project.  The thought would be that a console appplication
    /// would use this class to create the document for use within the Solution.  Basically this allows for generation of a 
    /// XSD from an activity definition made inside the BAM.XLA add in within Excel.  As items are added to the Activity this utility would be
    /// rerun to generate an updated XSD that would contain the new activity data definitions.
    /// </summary>
    public class SQLInsert_XSD_Builder
    {
        public static string BuildSQLInsertSchema()
        {

            //This stringbuilder will hold the XML output for loading into a document.  An alternative approach would be to use
            //strongly typed XML Elements and Attributes.
            StringBuilder _returnXML = new StringBuilder();

            //Header Information
            _returnXML.Append(@"<xs:schema xmlns:tns=""Http://Healthcare"" xmlns:b=""http://schemas.microsoft.com/BizTalk/2003"" xmlns:upgd=""urn:schemas-microsoft-com:xml-updategram"" attributeFormDefault=""unqualified"" elementFormDefault=""qualified"" targetNamespace=""Http://Healthcare"" xmlns:xs=""http://www.w3.org/2001/XMLSchema"">");
            _returnXML.Append(@"<xs:import schemaLocation=""..\updategram_identity.xsd"" namespace=""urn:schemas-microsoft-com:xml-updategram"" />");
            _returnXML.Append(@"<xs:annotation><xs:appinfo><b:references><b:reference targetNamespace=""urn:schemas-microsoft-com:xml-updategram"" /></b:references></xs:appinfo></xs:annotation><xs:element name=""Request""><xs:complexType><xs:sequence>");
            _returnXML.Append(@"<xs:element xmlns:updategram=""urn:schemas-microsoft-com:xml-updategram"" updategram:Prefix=""updg"" minOccurs=""1"" maxOccurs=""unbounded"" name=""sync""><xs:complexType><xs:sequence><xs:element updategram:Prefix=""updg"" minOccurs=""0"" maxOccurs=""unbounded"" name=""after""><xs:complexType><xs:sequence>");


            //Get a list of tables from the HealthcareClaims Database
            ArrayList _tables = GetTableList();

            

            foreach (string name in _tables)
            {
                string tableName = name;
                Console.WriteLine("Working on:" + tableName);
                _returnXML.Append(@"<xs:element minOccurs=""0"" maxOccurs=""unbounded"" name=""" + tableName + @"""><xs:complexType>");
                _returnXML.Append(LoopThroughTable(tableName));
                _returnXML.Append(@"</xs:complexType></xs:element>");
            }
            

            //Trailer Information
            _returnXML.Append(@"</xs:sequence></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element><xs:element name=""Response""><xs:complexType><xs:sequence><xs:element name=""Success"" type=""xs:anyType"" /></xs:sequence></xs:complexType></xs:element></xs:schema>");
            return _returnXML.ToString();

        }
        
        //Change to use connectionString in configuration file
        //const string _conStr = "Server=.;Database=HealthcareClaims;Trusted_Connection=True;";
        private static string GetDbConnection()
        {
            String conStr = String.Empty;

            try
            {
                conStr = ConfigurationManager.ConnectionStrings["HealthCareClaims"].ConnectionString;
            }
            catch (Exception e)
            {
                throw new Exception("BTSUtilities::SQLInsert_XSD_Builder - Connection String Not Found for HealthCareClaims or connection string invalid", e);
            }

            return conStr;
        }

        public static string LoopThroughTable(string TableName)
        {

            StringBuilder _bldr = new StringBuilder();
            

            string _cmd = "Select * from " + TableName;
            System.Data.SqlClient.SqlConnection _conn = new System.Data.SqlClient.SqlConnection(GetDbConnection());

            DataAdapter _adapter = new System.Data.SqlClient.SqlDataAdapter(_cmd, GetDbConnection());
            DataSet _ds = new DataSet();
            _adapter.FillSchema(_ds, SchemaType.Source);
            //Now we have a table lets iterate
            foreach (DataTable dt in _ds.Tables)
            {
                foreach (DataColumn col in dt.Columns)
                {
                    _bldr.Append(@"<xs:attribute name=""" + col.ColumnName + @""" ");
                    if (col.DataType.Name.ToString().ToLower().Contains("int"))
                    {
                        _bldr.Append(@"type=""xs:int""/>");
                    }
                    else //SQLXML uses all other types as strings
                    {
                        _bldr.Append(@"type=""xs:string""/>");
                    }

                    Console.WriteLine("\t\tCreating attribute:" + col.ColumnName + " - " + col.DataType);
                }
                _bldr.Append(@"<xs:attribute ref=""updategram:at-identity"" />");

            }
            return _bldr.ToString(); ;




        }
        public static ArrayList GetTableList()
        {
            ArrayList _tableList = new ArrayList();
            string query = "select Table_Name  from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' order by Table_Name";
            System.Data.SqlClient.SqlConnection _conn = new System.Data.SqlClient.SqlConnection(GetDbConnection());

            DataAdapter _adapter = new System.Data.SqlClient.SqlDataAdapter(query, GetDbConnection());
            DataSet _ds = new DataSet();
            _adapter.Fill(_ds);

            foreach (DataRow dr in _ds.Tables[0].Rows)
            {
                _tableList.Add(dr[0].ToString());
            }
            return _tableList;
        }
        
        
    }
}
